package top.sehnsucht.dao.impl;

import org.apache.log4j.Logger;
import top.sehnsucht.dao.UserPhotoDao;
import top.sehnsucht.util.DBUtil;
import top.sehnsucht.vo.Photo;
import top.sehnsucht.vo.UserInfo;
import top.sehnsucht.vo.UserPhoto;

import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/11/12
 */

public class UserPhotoDaoImpl implements UserPhotoDao {
    private Logger logger = Logger.getLogger(this.getClass());

    /**
     * 返回ArrayList<ArrayList<String>>,方便转成json传到前端
     * 只有用户信息
     *
     * @return
     */
    @Override
    public ArrayList<Map<String, String>> getAllUser() {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        ResultSet rs = null;
        ArrayList<Map<String, String>> arrs = new ArrayList<>();
        try {
            conn = DBUtil.getConn();
            String sql = "select id,username,birthday from userinfo order by id";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                Map<String, String> map = new HashMap();
                Integer id = rs.getInt(1);
                String username = rs.getString(2);
                java.util.Date birthday = rs.getDate(3);
                map.put("id", id.toString());
                map.put("username", username);
                map.put("birthday",birthday.toString());
                arrs.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return arrs;
    }

    /**
     * 添加photo到数据库中
     *
     * @param userPhotos
     */
    @Override
    public void addUserAndPhoto(ArrayList<UserPhoto> userPhotos) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            for (int i = 0; i < userPhotos.size(); i++) {
                String sql = "insert into userphoto (userid, photoname, photourl) values (?,?,?)";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, userPhotos.get(i).getUserid());
                ps.setString(2, userPhotos.get(i).getPhotoName());
                ps.setString(3, userPhotos.get(i).getPhotoUrl());
                int count = ps.executeUpdate();
                if (count > 0) {
                    System.out.println("插入成功！");
                } else {
                    System.out.println("插入失败！");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 根据id查找图片集合
     *
     * @param id
     * @return
     */
    @Override
    public ArrayList<UserPhoto> getPhotoByUserId(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        ResultSet rs = null;
        ArrayList<UserPhoto> userPhotos = new ArrayList<UserPhoto>();
        try {
            conn = DBUtil.getConn();
//            String sql = "select * from photo where id = " + id;
            String sql = "select userid, photoname, photourl from userphoto where userid = " + id;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                UserPhoto userPhoto = new UserPhoto(rs.getInt(1),rs.getString(2),rs.getString(3));
                userPhotos.add(userPhoto);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return userPhotos;
    }

    /**
     * 插入用户,返回用户id(主键)
     *
     * @param userInfo
     * @return
     */
    @Override
    public Integer addUser(UserInfo userInfo) {
        Connection conn = null;
        PreparedStatement ps = null;
        Integer ansId = null;
        try {
            conn = DBUtil.getConn();
            String sql = "insert into userinfo (username,birthday) values(?,?)";
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, userInfo.getUsername());
            ps.setObject(2, userInfo.getBirthday());
            int count = ps.executeUpdate();
            if (count > 0) {
                System.out.println("插入成功！");
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    //获取主键,设置了主键自增
                    ansId = rs.getInt(1);
                }
            } else {
                System.out.println("插入失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ansId;
    }

    /**
     * 根据要求查看用户
     * @param name
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public ArrayList<Map<String, String>> getSomeUser(String name, String startDate, String endDate) {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        ResultSet rs = null;
        ArrayList<Map<String, String>> arrs = new ArrayList<>();
        logger.debug("正常进入");
        try {
            conn = DBUtil.getConn();
            String sql = "select id,username,birthday from userinfo where ";
            if (name != null && !name.equals("")) {
                sql += " username like '%" + name + "%' ";
            } else {
                sql += " username like '%' ";
            }
            if (startDate!= null) {
                sql += " and birthday >= '" + java.sql.Date.valueOf(startDate) + "'";
            }
            if (endDate!= null) {
                sql += " and birthday <= '" + java.sql.Date.valueOf(endDate) + "'";
            }
            sql += " order by id ";
            logger.info("sql: " + sql);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                Map<String, String> map = new HashMap();
                Integer id = rs.getInt(1);
                String username = rs.getString(2);
                java.util.Date birthday = rs.getDate(3);
                map.put("id", id.toString());
                map.put("username", username);
                map.put("birthday",birthday.toString());
                arrs.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return arrs;
    }


}
